﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    public class ProductDeductRepository : BaseRepository<BaseEntity>, IProductDeductRepository
    {
        public ProductDeductRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 获取待扣减的数据
        /// </summary>
        /// <returns></returns>
        public List<SysStock> GetProductDeductInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                //获取关联库存中在线边库的库存信息
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select top 500 * from  sys_stock s (nolock) ");
                SqlStringBuilder.Append("inner join(select distinct b.src_zone_id,b.src_zone_name ");
                SqlStringBuilder.Append("from wms_product_deduct a (nolock) left join sys_pdline b (nolock) on a.pdline_code=b.pdline_code AND b.enabled='Y' ) al  ");
                SqlStringBuilder.Append("on s.zone_id=al.src_zone_id where s.qty!=0 ");
                SqlStringBuilder.Append("order by create_time ");

                return dbConn.Query<SysStock>(SqlStringBuilder.ToString()).ToList();
            }
        }

        /// <summary>
        /// 处理待扣减的数据
        /// </summary>
        /// <param name="sapSu"></param>
        /// <returns></returns>
        public bool InsertProductDeductInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                WmsProductDeduct wmsProduct = new WmsProductDeduct();
                List<string> sqlList = new List<string>();
                List<DynamicParameters> parametersList = new List<DynamicParameters>();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                DynamicParameters parameters = new DynamicParameters();
                int firstCount = 0;//初始行
                int rowCount = 10000;//前10000行

                bool blLoop = true;
                while (blLoop)
                {
                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append(" select a.* ");
                    SqlStringBuilder.Append(" from wms_product_deduct a (nolock) order by a.create_time,1 OFFSET " + firstCount + " ROWS FETCH NEXT "+ rowCount + " ROWS ONLY ");
                    List<WmsProductDeduct> wmsProductlists = dbConn.Query<WmsProductDeduct>(SqlStringBuilder.ToString()).ToList();

                    for (int i = 0; i < wmsProductlists.Count; i++)
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append(" select * from sys_pdline (nolock) where pdline_code='" + wmsProductlists[i].PdlineCode + "' AND enabled='Y' ");
                        List<SysPdline> sysPdlines = dbConn.Query<SysPdline>(SqlStringBuilder.ToString()).ToList();
                        SysStock stocks = null;
                        sqlList = new List<string>();
                        parametersList = new List<DynamicParameters>();
                        WmsProductDeduct wmsProducts = wmsProductlists[i];
                        string srcZoneNameList = "";
                        for (int s = 0; s < sysPdlines.Count; s++)
                        {
                            string SrcZoneNameStr = sysPdlines[s].SrcZoneName;
                            if (!string.IsNullOrEmpty(SrcZoneNameStr))
                            {
                                srcZoneNameList += "'" + sysPdlines[s].SrcZoneName + "',";
                            }
                        }

                        if (srcZoneNameList != "")
                        {
                            srcZoneNameList = srcZoneNameList.Substring(0, srcZoneNameList.Length - 1);
                            SqlStringBuilder = new StringBuilder(1024);
                            SqlStringBuilder.Append("select ruid,carton_no,part_no,qty from sys_stock (nolock) where zone_name in (" + srcZoneNameList + ") and part_no='" + wmsProducts.ItemPartNo + "' and qty>0 order by create_time ");
                            stocks = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).FirstOrDefault();
                        }


                        if (stocks != null)
                        {
                            decimal qty = stocks.Qty - Convert.ToDecimal(wmsProducts.ItemQty);
                            decimal handledItemQty = 0;
                            if (qty >= 0)//库存数>=待扣减数
                            {
                                SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                SqlStringBuilder.Append(" update sys_stock set qty=@qty where ruid=@ruid");
                                parameters = new DynamicParameters();
                                parameters.Add("@qty", qty);
                                parameters.Add("@ruid", stocks.Ruid);
                                //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                sqlList.Add(SqlStringBuilder.ToString());
                                parametersList.Add(parameters);

                                SqlStringBuilder = new StringBuilder(1024);//已扣减的数据，写入记录扣减日志表
                                SqlStringBuilder.Append("INSERT INTO dbo.wms_product_deduct_log(serial_number,part_id,part_no,unit,qty,item_part_id,item_part_no ");
                                SqlStringBuilder.Append("           ,item_unit,item_qty,pdline_code,bom_id,factory_id,factory_code,create_userid,create_time,guid) ");
                                SqlStringBuilder.Append("     VALUES(@serialNumber,@partId,@partNo,@unit,@qty,@itemPartId,@itemPartNo,@itemUnit,@itemQty ");
                                SqlStringBuilder.Append("           ,@pdlineCode,@bomId,@factoryId,@factoryCode,@createUserid,@createTime,@guid) ");
                                parameters = new DynamicParameters();
                                parameters.Add("@serialNumber", wmsProducts.SerialNumber);
                                parameters.Add("@partId", wmsProducts.PartId);
                                parameters.Add("@partNo", wmsProducts.PartNo);
                                parameters.Add("@unit", wmsProducts.Unit);
                                parameters.Add("@qty", wmsProducts.Qty);
                                parameters.Add("@itemPartId", wmsProducts.ItemPartId);
                                parameters.Add("@itemPartNo", wmsProducts.ItemPartNo);
                                parameters.Add("@itemUnit", wmsProducts.ItemUnit);
                                parameters.Add("@itemQty", wmsProducts.ItemQty);
                                parameters.Add("@pdlineCode", wmsProducts.PdlineCode);
                                parameters.Add("@bomId", wmsProducts.BomId);
                                parameters.Add("@factoryId", wmsProducts.FactoryId);
                                parameters.Add("@factoryCode", wmsProducts.FactoryCode);
                                parameters.Add("@createUserid", 0);
                                parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                parameters.Add("@guid", Guid.NewGuid().ToString());
                                //dbConn.Execute(SqlStringBuilder.ToString(), parameters);
                                sqlList.Add(SqlStringBuilder.ToString());
                                parametersList.Add(parameters);

                                SqlStringBuilder = new StringBuilder(1024);//删除已扣减的数据
                                SqlStringBuilder.Append(" delete from wms_product_deduct where ruid=@ruid");
                                parameters = new DynamicParameters();
                                parameters.Add("@ruid", wmsProductlists[i].Ruid);
                                //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                sqlList.Add(SqlStringBuilder.ToString());
                                parametersList.Add(parameters);

                                this.ExecuteTransaction(sqlList, parametersList);//执行
                            }
                            else//库存数<待扣减数
                            {
                                sqlList = new List<string>();
                                parametersList = new List<DynamicParameters>();

                                SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                SqlStringBuilder.Append(" update sys_stock set qty=@qty , enabled='N' where ruid=@ruid");
                                parameters = new DynamicParameters();
                                parameters.Add("@qty", 0);
                                parameters.Add("@ruid", stocks.Ruid);
                                //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                sqlList.Add(SqlStringBuilder.ToString());
                                parametersList.Add(parameters);

                                handledItemQty = Convert.ToDecimal(wmsProducts.HandledItemQty == null ? 0 : wmsProducts.HandledItemQty) + stocks.Qty;
                                SqlStringBuilder = new StringBuilder(1024);//更新 已处理扣减数据
                                SqlStringBuilder.Append(" update wms_product_deduct set handled_item_qty=@handledItemQty where ruid=@ruid");
                                parameters = new DynamicParameters();
                                parameters.Add("@handledItemQty", handledItemQty);
                                parameters.Add("@ruid", wmsProducts.Ruid);
                                //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                sqlList.Add(SqlStringBuilder.ToString());
                                parametersList.Add(parameters);
                                this.ExecuteTransaction(sqlList, parametersList);//执行
                                while (true)
                                {
                                    sqlList = new List<string>();
                                    parametersList = new List<DynamicParameters>();

                                    SqlStringBuilder = new StringBuilder(1024);
                                    SqlStringBuilder.Append("select * from wms_product_deduct (nolock) where ruid=" + wmsProducts.Ruid);
                                    WmsProductDeduct wmsProductDeduct = dbConn.Query<WmsProductDeduct>(SqlStringBuilder.ToString()).FirstOrDefault();

                                    if (wmsProductDeduct.ItemQty > wmsProductDeduct.HandledItemQty)
                                    {
                                        srcZoneNameList = "";
                                        for (int s = 0; s < sysPdlines.Count; s++)
                                        {
                                            string SrcZoneNameStr = sysPdlines[s].SrcZoneName;
                                            if (!string.IsNullOrEmpty(SrcZoneNameStr))
                                            {
                                                srcZoneNameList += "'" + sysPdlines[s].SrcZoneName + "',";//一个产线对应多个库区
                                            }
                                        }

                                        if (srcZoneNameList != "")
                                        {
                                            srcZoneNameList = srcZoneNameList.Substring(0, srcZoneNameList.Length - 1);
                                            SqlStringBuilder = new StringBuilder(1024);//判断在库的条码
                                            SqlStringBuilder.Append("select ruid,carton_no,part_no,qty from sys_stock (nolock) where zone_name in (" + srcZoneNameList + ") and part_no='" + wmsProducts.ItemPartNo + "' and qty>0 order by create_time ");
                                            stocks = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).FirstOrDefault();
                                        }

                                        if (stocks != null)
                                        {
                                            qty = stocks.Qty - Convert.ToDecimal(wmsProductDeduct.ItemQty - wmsProductDeduct.HandledItemQty);
                                            handledItemQty = 0;
                                            if (qty >= 0)
                                            {
                                                SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                                SqlStringBuilder.Append(" update sys_stock set qty=@qty where ruid=@ruid");
                                                parameters = new DynamicParameters();
                                                parameters.Add("@qty", qty);
                                                parameters.Add("@ruid", stocks.Ruid);
                                                //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                                sqlList.Add(SqlStringBuilder.ToString());
                                                parametersList.Add(parameters);

                                                SqlStringBuilder = new StringBuilder(1024);//已扣减的数据，写入记录扣减日志表
                                                SqlStringBuilder.Append("INSERT INTO dbo.wms_product_deduct_log(serial_number,part_id,part_no,unit,qty,item_part_id,item_part_no ");
                                                SqlStringBuilder.Append("           ,item_unit,item_qty,pdline_code,bom_id,factory_id,factory_code,create_userid,create_time,guid) ");
                                                SqlStringBuilder.Append("     VALUES(@serialNumber,@partId,@partNo,@unit,@qty,@itemPartId,@itemPartNo,@itemUnit,@itemQty ");
                                                SqlStringBuilder.Append("           ,@pdlineCode,@bomId,@factoryId,@factoryCode,@createUserid,@createTime,@guid) ");
                                                parameters = new DynamicParameters();
                                                parameters.Add("@serialNumber", wmsProductDeduct.SerialNumber);
                                                parameters.Add("@partId", wmsProductDeduct.PartId);
                                                parameters.Add("@partNo", wmsProductDeduct.PartNo);
                                                parameters.Add("@unit", wmsProductDeduct.Unit);
                                                parameters.Add("@qty", wmsProductDeduct.Qty);
                                                parameters.Add("@itemPartId", wmsProductDeduct.ItemPartId);
                                                parameters.Add("@itemPartNo", wmsProductDeduct.ItemPartNo);
                                                parameters.Add("@itemUnit", wmsProductDeduct.ItemUnit);
                                                parameters.Add("@itemQty", wmsProductDeduct.ItemQty);
                                                parameters.Add("@pdlineCode", wmsProductDeduct.PdlineCode);
                                                parameters.Add("@bomId", wmsProductDeduct.BomId);
                                                parameters.Add("@factoryId", wmsProductDeduct.FactoryId);
                                                parameters.Add("@factoryCode", wmsProductDeduct.FactoryCode);
                                                parameters.Add("@createUserid", 0);
                                                parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                                parameters.Add("@guid", Guid.NewGuid().ToString());
                                                //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                                sqlList.Add(SqlStringBuilder.ToString());
                                                parametersList.Add(parameters);

                                                SqlStringBuilder = new StringBuilder(1024);//删除已扣减的数据
                                                SqlStringBuilder.Append(" delete from wms_product_deduct where ruid=@ruid");
                                                parameters = new DynamicParameters();
                                                parameters.Add("@ruid", wmsProductDeduct.Ruid);
                                                //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                                sqlList.Add(SqlStringBuilder.ToString());
                                                parametersList.Add(parameters);

                                                this.ExecuteTransaction(sqlList, parametersList);//执行
                                                break;
                                            }
                                            else
                                            {
                                                SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                                SqlStringBuilder.Append(" update sys_stock set qty=@qty , enabled='N' where ruid=" + stocks.Ruid);
                                                parameters = new DynamicParameters();
                                                parameters.Add("@qty", 0);
                                                parameters.Add("@ruid", stocks.Ruid);
                                                //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                                sqlList.Add(SqlStringBuilder.ToString());
                                                parametersList.Add(parameters);

                                                handledItemQty = Convert.ToDecimal(wmsProductDeduct.HandledItemQty == null ? 0 : wmsProductDeduct.HandledItemQty) + stocks.Qty;
                                                SqlStringBuilder = new StringBuilder(1024);//更新 已处理扣减数据
                                                SqlStringBuilder.Append(" update wms_product_deduct set handled_item_qty=@handledItemQty where ruid=@ruid");
                                                parameters = new DynamicParameters();
                                                parameters.Add("@handledItemQty", handledItemQty);
                                                parameters.Add("@ruid", wmsProducts.Ruid);
                                                //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                                sqlList.Add(SqlStringBuilder.ToString());
                                                parametersList.Add(parameters);

                                                this.ExecuteTransaction(sqlList, parametersList);//执行
                                                continue;
                                            }
                                        }
                                        else
                                        {
                                            break;
                                        }
                                    }
                                    else
                                    {
                                        break;
                                    }
                                }
                            }
                        }
                    }

                    SqlStringBuilder = new StringBuilder(1024);//获取待扣减关联库存数据
                    SqlStringBuilder.Append("select top 500 * from  sys_stock s (nolock) ");
                    SqlStringBuilder.Append("inner join(select distinct b.src_zone_id,b.src_zone_name  ");
                    SqlStringBuilder.Append("from wms_product_deduct a (nolock) left join sys_pdline b (nolock) on a.pdline_code=b.pdline_code and b.enabled='Y') al  ");
                    SqlStringBuilder.Append("on s.zone_id=al.src_zone_id  ");
                    SqlStringBuilder.Append("order by create_time ");
                    List<SysStock> sysStocks = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).ToList();

                    SqlStringBuilder = new StringBuilder(1024);//获取待扣减行数
                    SqlStringBuilder.Append("  select count(1) as rowsCount from wms_product_deduct (nolock) ");
                    WmsProductDeduct wmsProductsCount = dbConn.Query<WmsProductDeduct>(SqlStringBuilder.ToString()).FirstOrDefault();

                    firstCount += 10000;
                    if (sysStocks.Count == 0 || wmsProductsCount.rowsCount <= firstCount)//当待扣减关联库存数据为0或循环行数>=待扣减行数，则跳出循环等待下一次作业
                    {
                        break;
                    }
                }

                return true;
            }
        }
    }
}
